﻿/*********************************************************************************************************************
Unit tests VanguardDocumentation, View Level2Entities

© 2014 Erik Ackerholm

This file is part of SqlVanguard.

SqlVanguard is free software: you can redistribute it and/or modify it under the terms of the GNU General Public 
License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later 
version.

SqlVanguard is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied 
warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with SqlVanguard. If not, see 
<http://www.gnu.org/licenses/>.
*********************************************************************************************************************/

set nocount on;
set xact_abort off;
GO



-- Test of existence
declare @ExpectedResult as table (
	Level0Type        varchar(64) not null,
	Level0Name        nvarchar(128) not null,
	Level1Type        varchar(64) not null,
	Level1Name        nvarchar(128) not null,
	Level2Type        varchar(64) not null,
	Level2Name        nvarchar(128) not null
);

insert into @ExpectedResult
values
	('SCHEMA', 'TestSchema', 'TABLE', 'TestTable', 'COLUMN', 'TestID'),
	('SCHEMA', 'TestSchema', 'TABLE', 'TestTable', 'COLUMN', 'TestCol'),
	('SCHEMA', 'TestSchema', 'TABLE', 'TestTable', 'CONSTRAINT', 'PK_TestTable'),
	('SCHEMA', 'TestSchema', 'TABLE', 'TestTable', 'CONSTRAINT', 'UQ_TestTable'),
	('SCHEMA', 'TestSchema', 'TABLE', 'TestTable', 'CONSTRAINT', 'DF_TestTable_TestCol'),
	('SCHEMA', 'TestSchema', 'TABLE', 'TestTable', 'CONSTRAINT', 'CK_TestTable_TestColNotEmpty'),
	('SCHEMA', 'TestSchema', 'TABLE', 'TestTable', 'INDEX', 'PK_TestTable'),
	('SCHEMA', 'TestSchema', 'TABLE', 'TestTable', 'INDEX', 'UQ_TestTable');

-- Unexpected rows
if
	exists (
		select Level0Type, Level0Name, Level1Type, Level1Name, Level2Type, Level2Name
		from VanguardDocumentation.Level2Entities where Level0Name like 'Test%'
		except
		select * from @ExpectedResult
	)
begin
	raiserror ('Too many results in test', 16, 1);
end;

-- Missing rows
if
	exists (
		select * from @ExpectedResult
		except
		select Level0Type, Level0Name, Level1Type, Level1Name, Level2Type, Level2Name
		from VanguardDocumentation.Level2Entities where Level0Name like 'Test%'
	)
begin
	raiserror ('Too few results in test', 16, 2);
end;
GO



-- Test of ordering
declare @TypeOrder as table (
	Level0Type        varchar(64) not null,
	Level1Type        varchar(64) not null,
	Level2Type        varchar(64) not null,
	MinSearchOrder    integer not null,
	MaxSearchOrder    integer not null,
	primary key (Level0Type, Level1Type, Level2Type)
);

insert into @TypeOrder
select Level0Type, Level1Type, Level2Type, min(SearchOrder), max(SearchOrder)
from VanguardDocumentation.Level2Entities
group by Level0Type, Level1Type, Level2Type;

-- Same order for different types
if
	exists (
		select *
		from @TypeOrder as a
			inner join @TypeOrder as b on (a.MinSearchOrder = b.MinSearchOrder)
		where a.Level0Type <> b.Level0Type or a.Level1Type <> b.Level1Type or a.Level2Type <> b.Level2Type
	)
begin
	raiserror ('Types have same SearchOrder', 16, 3);
end;

-- Different order for same type
if
	exists (
		select *
		from @TypeOrder
		where MinSearchOrder <> MaxSearchOrder
	)
begin
	raiserror ('Different order within the same type', 16, 4);
end;
GO



-- Test of ordering at level 1
declare @TypeOrder as table (
	Level0Type        varchar(64) not null,
	Level1Type        varchar(64) not null,
	MinSearchOrder    integer not null,
	MaxSearchOrder    integer not null,
	primary key (Level0Type, Level1Type)
);

insert into @TypeOrder
select Level0Type, Level1Type, min(SearchOrder), max(SearchOrder)
from VanguardDocumentation.Level2Entities
group by Level0Type, Level1Type;

-- Interleaved SearchOrder for Level1Type
if
	exists (
		select *
		from @TypeOrder as a
			inner join @TypeOrder as b on (a.MinSearchOrder between b.MinSearchOrder and b.MaxSearchOrder)
		where a.Level0Type <> b.Level0Type or a.Level1Type <> b.Level1Type
	)
begin
	raiserror ('Level 1 not in order', 16, 5);
end;

-- Different order between Level1Entities and Level2Entities
if
	exists (
		select *
		from @TypeOrder as a2
			inner join @TypeOrder as b2 on (a2.MinSearchOrder < b2.MinSearchOrder)
			inner join VanguardDocumentation.Level1Entities as b1 on (b2.Level0Type = b1.Level0Type and b2.Level1Type = b1.Level1Type)
			inner join VanguardDocumentation.Level1Entities as a1 on (a2.Level0Type = a1.Level0Type and a2.Level1Type = a1.Level1Type)
		where a1.SearchOrder >= b1.SearchOrder
	)
begin
	raiserror ('Different order in Level1Entities', 16, 6);
end;
GO




